﻿CREATE FUNCTION StatusLog_Repo_FirstActive(
	@RepoID int
)RETURNS @TT TABLE(LogID int,	StateTo int, RepoWorkWeight numeric(32,2),DateTime datetime) AS BEGIN
DECLARE @T TABLE(ID int NOT NULL IDENTITY (1, 1), LogID int,	StateTo int, RepoWorkWeight numeric(32,2),DateTime datetime)
INSERT INTO @T
SELECT B.ID,B.StateTo,ISNULL(CONVERT(numeric(32,2),S_P.StateParameterValue),-1),DateTime
FROM tlog_State B
LEFT OUTER JOIN t_State_Parameters S_P ON B.StateTo = S_P.StateID AND 
                      S_P.StateParameterID = dbo.fs_StateParamID__RepoWorkWeight()
WHERE B.TablePK = @RepoID AND B.TableName = 't_Repossession'
ORDER BY B.ID DESC
;
WITH RepoActive AS(
SELECT TOP (1) ID,LogID,StateTo,RepoWorkWeight,DateTime FROM @T WHERE RepoWorkWeight > 0 ORDER BY ID
UNION ALL
SELECT T.ID ,T.LogID,T.StateTo,T.RepoWorkWeight,T.DateTime
FROM @T T INNER JOIN RepoActive RA ON T.ID = RA.ID+1 WHERE T.RepoWorkWeight > 0
)
INSERT INTO @TT SELECT TOP 1 LogID,StateTo,RepoWorkWeight,DateTime FROM RepoActive ORDER BY LogID
IF @@ROWCOUNT = 0
	INSERT INTO @TT	SELECT TOP 1 LogID,StateTo,RepoWorkWeight,DateTime FROM @T ORDER BY LogID
ELSE RETURN
IF @@ROWCOUNT = 0
	INSERT INTO @TT	
	SELECT R.ID,R.StatusID,ISNULL(CONVERT(numeric(32,2),S_P.StateParameterValue),-1),StatusDate
	FROM         dbo.t_Repossession AS R
	LEFT OUTER JOIN t_State_Parameters S_P ON R.StatusID = S_P.StateID AND 
												S_P.StateParameterID = dbo.fs_StateParamID__RepoWorkWeight()
	WHERE R.ID = @RepoID

RETURN
--DECLARE @LoanID int SET @LoanID = 1001059--1002801--1001160
--SELECT * FROM StatusLog_Repo(@LoanID)
--SELECT dbo.StatusLog_Repo_FirstActiveDate(ID) FROM t_Repossession R WHERE R.LoanID = @LoanID
END

